/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controlador;

import java.sql.*;

/**
 *
 * @author JUANCHO
 */
public class Conection {

    ResultSet result = null;
    private Connection conexion = null;
    Statement statement = null;
    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost/dbhostals";
    String userDB = "root";
    String passDB = "";

    public Conection() {
        conectar();
    }

    public int conectar() {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("No se pudo cargar el driver :-( " + driver);
            return -1;
        }
        try {
            conexion = DriverManager.getConnection(url, userDB, passDB);
        } catch (SQLException e) {
            System.out.println("No se pudo realizar la conexion :-(");
            return -2;
        }
        System.out.println("si conecto");
        return 1;
    }

    public void desconectar() throws SQLException {
        this.conexion.close();
        this.statement.close();
    }

    public Connection getConnection() throws SQLException {
        conexion = null;
        try {
            Class.forName(driver);
            conexion = DriverManager.getConnection(url, userDB, passDB);
        } catch (ClassNotFoundException e) {
            System.out.println("Error en el driver" + e.getMessage());
        }
        System.out.println("conectoooooooooooooooo");
        return conexion;

    }

    public SQLException insertar(String sql) {
        try {
            statement = getConexion().createStatement();
            statement.execute(sql);
            statement.close();
            return null;
        } catch (SQLException e) {
            return e;
        }
    }
    
    public ResultSet consultar(String sql) {
        try {
            statement = getConexion().createStatement();
            result = statement.executeQuery(sql);
        } catch (SQLException e) {
            System.out.println("Error en consulta conection" + e);
            return null;
        }
        return result;
    }

    public int verificar(String sql) {
        try {
            statement = getConexion().createStatement();
            result = statement.executeQuery(sql);
            if (result.next()) {
                return 1;
            }
            statement.close();
        } catch (SQLException e) {
            System.out.println(e);
        }
        return -1;
    }

    public int actualizar(String sql) {
        try {
            statement = getConexion().createStatement();
            statement.executeUpdate(sql);
            statement.close();
            return 1;
        } catch (SQLException e) {
            System.out.println("ERROR" + e);
        }
        return -1;
    }

    public StringBuffer reporteSQL(String sql) {
        StringBuffer sb = new StringBuffer();
        try {
            statement = getConexion().createStatement();
            result = statement.executeQuery(sql);
            int x = 1;
            boolean b = true;
            sb.append("<table>");
            while (result.next()) {
                sb.append("<tr>");
                x = 1;
                while (result.getMetaData().getColumnCount() >= x) {
                    sb.append("<td>");
                    sb.append(result.getString(x));
                    sb.append("</td>");
                    x++;
                }
                sb.append("<td>");
                sb.append("</td>");
                sb.append("</tr>");
            }
            sb.append("</table>");
        } catch (SQLException e) {
            sb.append(e.toString());
            return sb;
        }
        return sb;
    }

    public String Listar(String sql) {
        String sb = new String();
        try {
            statement = getConexion().createStatement();
            result = statement.executeQuery(sql);
            int x = 1;
            boolean b = true;
            while (result.next()) {
                x = 1;
                while (result.getMetaData().getColumnCount() >= x) {
                    sb += (result.getString(x) + "  ");
                    x++;
                }
                sb += ("\n");
            }

        } catch (SQLException e) {
            sb += (e.toString());
            return sb;
        }
        return sb;
    }

    public SQLException eliminar(String sql) {
        try {
            statement = conexion.createStatement();
            statement.execute(sql);
            statement.close();
            return null;
        } catch (SQLException e) {
            return e;
        }
    }

    public StringBuffer cmbGeneraIn(String tabla, int col) {
        StringBuffer sb = new StringBuffer();
        try {
            ResultSet resC = null;
            String sql = "Select doc_client from " + tabla.toString()+" where booking_st_code <> 101 and booking_st_code <> 102";
            statement = conexion.createStatement();
            resC = statement.executeQuery(sql);
           // sb.append("<select name=\"" + tabla.toString() + " id=\"" + tabla.toString() + "\">");
            sb.append("<option id=\"" + tabla.toString() + "\" value=\"0\">" + "seleccione" + "</option>");
            while (resC.next()) {
                sb.append("<option id=\"" + tabla.toString() + "\" value=\"" + resC.getString(col) + "\">" + resC.getString(col) + " </option>");
            }
           // sb.append("</select>");
        } catch (SQLException e) {
            sb.append(e.toString());
            return sb;
        }
        return sb;
    }
    public StringBuffer cmbGeneraOut(String tabla, int col) {
        StringBuffer sb2 = new StringBuffer();
        try {
            ResultSet resC2 = null;
            String sql2 = "Select doc_client from " + tabla.toString()+ " where booking_st_code= 101";
            statement = conexion.createStatement();
            resC2 = statement.executeQuery(sql2);
           // sb.append("<select name=\"" + tabla.toString() + " id=\"" + tabla.toString() + "\">");
            sb2.append("<option id=\"" + tabla.toString() + "\" value=\"0\">" + "seleccione" + "</option>");
            while (resC2.next()) {
                sb2.append("<option id=\"" + tabla.toString() + "\" value=\"" + resC2.getString(col) + "\">" + resC2.getString(col) + " </option>");
            }
           // sb.append("</select>");
        } catch (SQLException e) {
            sb2.append(e.toString());
            return sb2;
        }
        return sb2;
    }
    
    
    public StringBuffer reporte(String sql) {
        StringBuffer sb = new StringBuffer();
        try {
            Conection conexion = new Conection();
            Statement stmt = null;
            ResultSet rset = null;
            stmt = conexion.getConexion().createStatement();
            rset = stmt.executeQuery(sql);
            sb.append("<center><br><font>LISTADO</font>");
            sb.append("<br><br><br><table  width=50% align= center border = 2 bgcolor= E6E6E6>");
            int x = 1;
            while (rset.next()) {
                sb.append("<tr align = center>");
                x = 1;
                while (rset.getMetaData().getColumnCount() >= x) {
                    sb.append("<td>");
                    sb.append(rset.getString(x).toString());
                    sb.append("</td>");
                    x++;
                }
                sb.append("</tr>");
            }
            sb.append("</table>");
        } catch (SQLException e) {
            sb.append(e.toString());
            return sb;
        }
        return sb;
    }

    public ResultSet getResult() {
        return result;
    }

    public void setResult(ResultSet result) {
        this.result = result;
    }

    public Connection getConexion() {
        return conexion;
    }

    public void setConexion(Connection conexion) {
        this.conexion = conexion;
    }

    public Statement getStatement() {
        return statement;
    }

    public void setStatement(Statement statement) {
        this.statement = statement;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUserDB() {
        return userDB;
    }

    public void setUserDB(String userDB) {
        this.userDB = userDB;
    }

    public String getPassDB() {
        return passDB;
    }

    public void setPassDB(String passDB) {
        this.passDB = passDB;
    }   
}
